package com.ruoyi.web.pgci.service;

import com.alibaba.excel.metadata.data.ReadCellData;
import com.google.common.collect.Lists;
import com.ruoyi.common.core.domain.R;
import com.ruoyi.common.utils.DateUtils;
import com.ruoyi.common.utils.StringUtils;
import com.ruoyi.web.pgci.domain.*;
import com.ruoyi.web.pgci.mapper.*;
import com.ruoyi.web.pgci.vo.ReadExcelStyleVo;
import com.ruoyi.web.pgci.vo.ReadPendingActivitiesVo;
import com.ruoyi.web.pgci.vo.ReadTrainingMatrixVo;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.xssf.usermodel.*;
import org.springframework.beans.BeanUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.security.core.parameters.P;
import org.springframework.stereotype.Service;

import javax.annotation.Resource;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

/**
 * @author yyq
 * @create 2023-11-13
 * @description excel读取service
 */
@Slf4j
@Service
public class ReadExcelService {

    @Resource
    private PgciPostMapper pgciPostMapper;

    @Resource
    private PgciCoursesMapper pgciCoursesMapper;

    @Resource
    private PgciPostCourseLearnReqMapper pgciPostCourseLearnReqMapper;

    @Resource
    private PgciSailorsCourseLearnRecordMapper pgciSailorsCourseLearnRecordMapper;
    @Autowired
    private PgciSailorsMapper pgciSailorsMapper;

    /**
     * save training matrix
     *
     * @param cachedDataList training matrix data
     */
    public void saveTrainingMatrix(List<ReadTrainingMatrixVo> cachedDataList) {
        if (CollectionUtils.isEmpty(cachedDataList)) {
            return;
        }
        for (ReadTrainingMatrixVo vo : cachedDataList) {
            if (StringUtils.isBlank(vo.getCourseName()) || StringUtils.isBlank(vo.getOlpCode())) {
                continue;
            }
            PgciCourses pgciCoursesSearch = new PgciCourses();
            pgciCoursesSearch.setOlpCode(vo.getOlpCode());
            pgciCoursesSearch.setCourseName(vo.getCourseName());
            List<PgciCourses> coursesList = pgciCoursesMapper.selectPgciCoursesList(pgciCoursesSearch);
            if (CollectionUtils.isEmpty(coursesList)) {
                PgciCourses pgciCoursesInsert = new PgciCourses();
                BeanUtils.copyProperties(vo, pgciCoursesInsert);
                pgciCoursesInsert.setUpdateTime(DateUtils.getNowDate());
                pgciCoursesInsert.setCreateTime(DateUtils.getNowDate());
                pgciCoursesMapper.insertPgciCourses(pgciCoursesInsert);
            } else {
                PgciCourses pgciCoursesUpdate = new PgciCourses();
                BeanUtils.copyProperties(vo, pgciCoursesUpdate);
                pgciCoursesUpdate.setId(coursesList.get(0).getId());
                pgciCoursesUpdate.setUpdateTime(DateUtils.getNowDate());
                pgciCoursesMapper.updatePgciCourses(pgciCoursesUpdate);
            }
        }
    }

    /**
     * 保存培训岗位信息
     * @param rowIndex
     * @param columnIndex
     * @param stringValue
     */
    public void saveTrainingPost(Integer rowIndex, Integer columnIndex, String stringValue) {
        PgciPost postSearch = new PgciPost();
        postSearch.setPostName(stringValue);
        postSearch.setParentId(0L);
        List<PgciPost> pgciPosts = pgciPostMapper.selectPgciPostList(postSearch);
        if (CollectionUtils.isEmpty(pgciPosts)) {
            PgciPost insert = new PgciPost();
            insert.setCreateTime(DateUtils.getNowDate());
            insert.setUpdateTime(DateUtils.getNowDate());
            insert.setParentId(0L);
            insert.setPostName(stringValue);
            insert.setPosRow(rowIndex);
            insert.setPostCol(columnIndex);
            pgciPostMapper.insertPgciPost(insert);
        } else {
            PgciPost update = new PgciPost();
            update.setUpdateTime(DateUtils.getNowDate());
            update.setPosRow(rowIndex);
            update.setPostCol(columnIndex);
            update.setId(pgciPosts.get(0).getId());
            pgciPostMapper.updatePgciPost(update);
        }
    }

    /**
     * 保存岗位课程学习要求
     * @param inputStream
     */
    public void savePostCourseLearnReq(InputStream inputStream) throws IOException {
        //第0列是课程名称，作为唯一标识
        try {
            int uniqueCol = 0;
            Integer maxPosCol = pgciPostMapper.selectMaxPosCol();
            Integer minPosCol = pgciPostMapper.selectMinPosCol();
            Integer posRow = pgciPostMapper.selectLastPosRow(); //表头开始行
            List<ReadExcelStyleVo> readExcelStyleVoList = readExcelStyleRelation(inputStream, posRow + 1, minPosCol, maxPosCol, 0, uniqueCol);
            if (CollectionUtils.isEmpty(readExcelStyleVoList)) {
                return;
            }
            //删除关系
            pgciPostCourseLearnReqMapper.deletePgciPostCourseLearnReqAll();
            List<PgciPost> pgciPostList = pgciPostMapper.selectParentPgciPostList();
            List<PgciCourses> pgciCourseList = pgciCoursesMapper.selectPgciCoursesList(new PgciCourses());
            if (CollectionUtils.isEmpty(pgciCourseList) || CollectionUtils.isEmpty(pgciPostList)) {
                return;
            }
            List<PgciPostCourseLearnReq> learnReqList = Lists.newArrayList();
            for (ReadExcelStyleVo vo : readExcelStyleVoList) {
                if (StringUtils.isBlank(vo.getUniqueValue())) {
                    continue;
                }
                PgciPost post = pgciPostList.stream().filter(s -> s.getPosRow().equals(posRow) && s.getPostCol().equals(vo.getCol()))
                        .findFirst().orElse(null);
                PgciCourses course = pgciCourseList.stream().filter(s -> vo.getUniqueValue().equals(s.getCourseName())).findFirst().orElse(null);
                if (post == null || course == null) {
                    continue;
                }
                PgciPostCourseLearnReq learnReq = new PgciPostCourseLearnReq();
                learnReq.setCourseId(course.getId());
                learnReq.setPostId(post.getId());
                learnReq.setLearnRequire(getPostCourseLearnReq(vo.getMarkColor()));
                learnReq.setMarkColor(vo.getMarkColor());
                learnReq.setCellValue(vo.getCellValue());
                learnReq.setCreateTime(DateUtils.getNowDate());
                learnReqList.add(learnReq);
            }
            if (CollectionUtils.isEmpty(learnReqList)) {
                return;
            }
            //每500条进行批量插入
            List<List<PgciPostCourseLearnReq>> partitions = Lists.partition(learnReqList, 500);
            for (List<PgciPostCourseLearnReq> partition : partitions) {
                pgciPostCourseLearnReqMapper.batchInsertPgciPostCourseLearnReq(partition);
            }
        }catch (Exception e){
             log.error("读取excel失败{}",e.getMessage());
        }finally {
               inputStream.close();
        }

    }


    /**
     * save sailors Pending activities
     *
     * @param cachedDataList
     */
    public void savePendingActivities(List<ReadPendingActivitiesVo> cachedDataList) {
        if (CollectionUtils.isEmpty(cachedDataList)) {
            return;
        }
        List<PgciSailors> batchInsertList = new ArrayList<>();
        List<PgciSailors> batchUpdateList = new ArrayList<>();
        for (ReadPendingActivitiesVo vo : cachedDataList) {
            if (StringUtils.isBlank(vo.getUserId())) {
                continue;
            }
            PgciSailors pgciSailors = pgciSailorsMapper.selectPgciSailorsByUserId(vo.getUserId());
            PgciSailors sailorsEdit = new PgciSailors();
            BeanUtils.copyProperties(vo, sailorsEdit);
            if (pgciSailors == null) {
                sailorsEdit.setCreateTime(DateUtils.getNowDate());
                sailorsEdit.setUpdateTime(DateUtils.getNowDate());
                batchInsertList.add(sailorsEdit);
            } else {
                sailorsEdit.setUpdateTime(DateUtils.getNowDate());
                batchUpdateList.add(sailorsEdit);
            }
        }
        if (CollectionUtils.isNotEmpty(batchInsertList)) {
            pgciSailorsMapper.batchInsertPgciSailors(batchInsertList);
        }
        if (CollectionUtils.isNotEmpty(batchUpdateList)) {
            pgciSailorsMapper.batchUpdatePgciSailors(batchUpdateList);
        }

    }

    /**
     * save sailors course info
     * @param headMap excel table header
     */
    public void saveSailorsCourse(Map<Integer, ReadCellData<?>> headMap) {
        if (headMap == null) {
            return;
        }
        List<PgciCourses> pgciCoursesListInsert = Lists.newArrayList();
        List<PgciCourses> pgciCoursesListUpdate = Lists.newArrayList();
        List<PgciCourses> pgciCoursesList = pgciCoursesMapper.selectPgciCoursesList(new PgciCourses());
        for (Map.Entry<Integer, ReadCellData<?>> entry : headMap.entrySet()) {
            ReadCellData<?> cellData = entry.getValue();
            Integer columnIndex = cellData.getColumnIndex();
            Integer rowIndex = cellData.getRowIndex();
            String stringValue = cellData.getStringValue();
            if (StringUtils.isBlank(stringValue)){
                continue;
            }
            PgciCourses pgciCoursesEdit = new PgciCourses();
            pgciCoursesEdit.setPosRow(rowIndex);
            pgciCoursesEdit.setPosCol(columnIndex);
            PgciCourses pgciCourses = pgciCoursesList.stream().filter(s -> stringValue.equals(s.getCourseFullName())).findFirst().orElse(null);
            if (pgciCourses == null){
                pgciCoursesEdit.setCourseFullName(stringValue);
                pgciCoursesEdit.setCourseName(stringValue);
                pgciCoursesEdit.setOlpCode("-1");
                pgciCoursesEdit.setCreateTime(DateUtils.getNowDate());
                pgciCoursesEdit.setUpdateTime(DateUtils.getNowDate());
                pgciCoursesListInsert.add(pgciCoursesEdit);
            }else {
                pgciCoursesEdit.setId(pgciCourses.getId());
                pgciCoursesEdit.setUpdateTime(DateUtils.getNowDate());
                pgciCoursesListUpdate.add(pgciCoursesEdit);
            }

        }
        if (CollectionUtils.isNotEmpty(pgciCoursesListInsert)){
            pgciCoursesMapper.batchInsertPgciCourses(pgciCoursesListInsert);
        }
        if (CollectionUtils.isNotEmpty(pgciCoursesListUpdate)){
            List<List<PgciCourses>> partition = Lists.partition(pgciCoursesListUpdate, 200);
            for (List<PgciCourses> pgciCourses : partition) {
                pgciCoursesMapper.batchUpdatePgciCourses(pgciCourses);
            }
        }
    }

    /**
     * 保存船员课程学习记录
     * @param inputStream
     */
    public void saveSailorsCourseLearnRecord(InputStream inputStream) throws IOException {
        try{
            //第1列是用户ID，作为唯一标识
            int uniqueCol = 1;
            Integer maxPosCol = pgciCoursesMapper.selectMaxPosCol();
            Integer minPosCol = pgciCoursesMapper.selectMinPosCol();
            Integer posRow = pgciCoursesMapper.selectLastPosRow(); //表头开始行
            List<ReadExcelStyleVo> readExcelStyleVoList = readExcelStyleRelation(inputStream, posRow + 1, minPosCol, maxPosCol, 1, uniqueCol);
            if (CollectionUtils.isEmpty(readExcelStyleVoList)) {
                return;
            }
            List<PgciSailors> pgciSailorsList = pgciSailorsMapper.selectPgciSailorsList(new PgciSailors());
            List<PgciCourses> pgciCourseList = pgciCoursesMapper.selectPgciCoursesList(new PgciCourses());
            if (CollectionUtils.isEmpty(pgciCourseList) || CollectionUtils.isEmpty(pgciSailorsList)) {
                return;
            }
            List<PgciSailorsCourseLearnRecord> learnRecordList = Lists.newArrayList();
            for (ReadExcelStyleVo vo : readExcelStyleVoList) {
                if (StringUtils.isBlank(vo.getUniqueValue())) {
                    continue;
                }
                //通过ID查询船员
                PgciSailors sailor = pgciSailorsList.stream().filter(s -> !StringUtils.isBlank(s.getUserId()) && s.getUserId().equals(vo.getUniqueValue())).findFirst().orElse(null);
                //通过坐标查询课程
                PgciCourses course = pgciCourseList.stream().filter(s -> s.getPosRow() != null && s.getPosCol() != null &&
                        s.getPosRow().equals(posRow) && s.getPosCol().equals(vo.getCol())).findFirst().orElse(null);

                if (course == null || sailor == null) {
                    continue;
                }

                PgciSailorsCourseLearnRecord learnRecordInsert = new PgciSailorsCourseLearnRecord();
                learnRecordInsert.setCourseId(course.getId());
                learnRecordInsert.setUserId(sailor.getUserId());
                learnRecordInsert.setCompleteStatus(getSailorsCourseCompleteStatus(vo.getCellValue(),vo.getMarkColor()));
                learnRecordInsert.setMarkColor(vo.getMarkColor());
                learnRecordInsert.setCellValue(vo.getCellValue());
                learnRecordInsert.setCreateTime(DateUtils.getNowDate());
                learnRecordInsert.setUpdateTime(DateUtils.getNowDate());
                learnRecordList.add(learnRecordInsert);
            }
            if (CollectionUtils.isEmpty(learnRecordList)) {
                return;
            }
            //每1000条进行批量插入
            List<List<PgciSailorsCourseLearnRecord>> partitions = Lists.partition(learnRecordList, 1000);
            for (List<PgciSailorsCourseLearnRecord> partition : partitions) {
                pgciSailorsCourseLearnRecordMapper.batchInsertPgciSailorsCourseLearnRecord(partition);
            }
        }catch (Exception e){
            log.error("excel读取失败{}",e.getMessage());
        }finally {
            inputStream.close();
        }

    }



    /**
     * 读取excel样式关系
     *
     * @param file      excel文件
     * @param startRow  开始行
     * @param startCol  开始列
     * @param endCol    结束行
     * @param sheetNum  读取sheet numbeer
     * @param uniqueCol 这一行唯一标识列索引
     * @return
     */
    public List<ReadExcelStyleVo> readExcelStyleRelation(InputStream inputStream, int startRow, int startCol, int endCol, int sheetNum, int uniqueCol) throws IOException {
        try {
            List<ReadExcelStyleVo> readExcelStyleVoList = Lists.newArrayList();
            XSSFWorkbook xssfWorkbook = new XSSFWorkbook(inputStream);
            XSSFSheet sheet = xssfWorkbook.getSheetAt(sheetNum);
            if (sheet == null) {
                return null;
            }
            int rowCount = sheet.getPhysicalNumberOfRows();
            for (int rowNum = startRow; rowNum < rowCount; rowNum++) {
                XSSFRow xssfRow = sheet.getRow(rowNum);
                XSSFCell uniqueCell = xssfRow.getCell(uniqueCol);
                uniqueCell.setCellType(CellType.STRING);
                String uniqueValue = uniqueCell.getStringCellValue();
                if (StringUtils.isBlank(uniqueValue)) {
                    continue;
                }
                if (xssfRow == null || uniqueCell == null) {
                    continue;
                }
                for (int colNum = startCol; colNum <= endCol; colNum++) {
                    XSSFCell cell = xssfRow.getCell(colNum);
                    if (cell == null) {
                        continue;
                    }
                    cell.setCellType(CellType.STRING);
                    String stringCellValue = cell.getStringCellValue();
                    XSSFCellStyle cellStyle = cell.getCellStyle();
                    XSSFColor xssfColor = cellStyle.getFillForegroundColorColor();
                    byte[] rgbs;
                    String markColor = " ";
                    if (xssfColor != null && StringUtils.isNotEmpty(stringCellValue)) {
                        rgbs = xssfColor.getRGB();
                        markColor = String.format("#%02X%02X%02X", rgbs[0], rgbs[1], rgbs[2]);
                    }
                    ReadExcelStyleVo readExcelStyleVo = new ReadExcelStyleVo();
                    readExcelStyleVo.setRow(rowNum);
                    readExcelStyleVo.setCol(colNum);
                    readExcelStyleVo.setCellValue(StringUtils.isEmpty(stringCellValue)?"":stringCellValue);
                    readExcelStyleVo.setUniqueValue(uniqueValue);
                    readExcelStyleVo.setMarkColor(markColor);
                    readExcelStyleVoList.add(readExcelStyleVo);
                }
            }
            return readExcelStyleVoList;

        } catch (Exception e) {
            log.error("读取岗位课程关系失败{}", e.getMessage());
            return null;
        }finally {
            inputStream.close();
        }
    }


    /**
     * 学习要求
     * 0. NO relation
     * 1.To Be Completed Within One Month of Joining #7a7a7a
     * 2.Mandatory Completed Every 12 Months  #660033
     * 3.Mandatory Completed Every 24 Months #ff0000
     * 4.Recommended #3366ff
     * 5.Combined = Safety Officer Course #92d050
     *
     * @param markColor
     */
    private int getPostCourseLearnReq(String markColor) {
        if (StringUtils.isBlank(markColor)) {
            return 0;
        }
        int learnReq = 0;
        markColor = markColor.toLowerCase();
        if (markColor.equals("#7a7a7a")) {
            learnReq = 1;
        } else if (markColor.equals("#660033")) {
            learnReq = 2;
        } else if (markColor.equals("#ff0000")) {
            learnReq = 3;
        } else if (markColor.equals("#3366ff")) {
            learnReq = 4;
        } else if (markColor.equals("#92d050")) {
            learnReq = 5;
        }
        return learnReq;
    }

    /**
     * 1、（课程必学，按要求完成）Required activity - Approved - the date it was last done/approved
     *
     * 2、（课程必学，超时完成）Required activity - Outdated - the date it was last done/approved/disapproved
     *
     * 3、（课程必学，未完成）Required activity - Pending - it has not been done
     *
     * 4、（课程非必学，按要求完成了课程）Not required activity - Approved - the date it was last done/approved
     *
     * 5、（课程非必学，未完成）Not required activity - it has not been done
     * @param cellValue
     * @param markColor
     * @return
     */
    private int getSailorsCourseCompleteStatus(String cellValue,String markColor){
        if (!StringUtils.isBlank(cellValue) && cellValue.equals("NR")){
            return  5;
        }
        if (!StringUtils.isBlank(cellValue) && cellValue.equals("Pending")){
            return  3;
        }
        markColor = markColor.toLowerCase();
        int completeStatus = 5;
        if (markColor.equals("#ceefce")) {
            completeStatus = 1;
        } else if (markColor.equals("#eecccc")) {
            completeStatus = 2;
        } else if (markColor.equals("#ffeb9c")) {
            completeStatus = 4;
        }
        return completeStatus;
    }


}
